<?php

	/**
	 * Defines a class to be used for MSSQL databases
	 * 
	 * @author Pablo Santiago Sánchez <phackwer@gmail.com>
	 * @copyright Copyright (c) 2008, Pablo Santiago Sánchez
	 * @license http://opensource.org/licenses/bsd-license.php BSD License
	 * @package pop
	 * @subpackage drivers
	 */

	/**
	 * Defines a class to be used for MSSQL databases
	 * 
	 * @package pop
	 * @subpackage drivers
	 */
	class PMSSQLDBDriver extends PGenericDBDriver
	{
		public $schema = 'dbo';
		public $separator = '.';
		public $driver = 'mssql';
		
		/**
		 * Filter the field for update
		 * @param $value
		 * @param $type
		 * @param $defaultvalue
		 * @return string
		 */
		public function filterUpdate($value, $type, $defaultval = null)
		{
			if (is_null($value) || $value == "null" && is_null($defaultval))
				$value = "null";
				
			if (($type=="PText" || $type=="PVarchar" || $type=="PDate" || $type=="PTime" || $type=="PDatetime") && $value != "null")
				return "'".str_replace("'","''",($value))."'";
			else
				return parent::filterUpdate($value, $type, $defaultval);
		}
		
		/**
		 * Filter the field for insert
		 * @param $value
		 * @param $type
		 * @param $defaultvalue
		 * @return string
		 */
		public function filterInsert($value, $type, $defaultval = null)
		{
			if (is_null($value) || $value == "null" && is_null($defaultval))
				$value = "null";
				
			if (($type=="PText" || $type=="PVarchar" || $type=="PDate" || $type=="PTime" || $type=="PDatetime") && $value != "null")
				return "'".str_replace("'","''",($value))."'";
			else
				return parent::filterInsert($value, $type, $defaultval);
		}
		
		/**
		 * Filter the field for select
		 * @param $tablename
		 * @param field
		 * @param $key
		 * @param $type
		 * @return string
		 */
		public function filterSelect($tablename, $field, $key, $type)
		{
			if ($type == "PDatetime" || $type == "PDate" || $type == "PTime")
				return "convert(nvarchar(19), ".$tablename.".".$field.", 20) as ".$key;
			else
				return parent::filterSelect($tablename, $field, $key, $type);
		}
		
		/**
		 * Returns the last inserted itme id
		 * @param $pdodb
		 * @param $table
		 * @param $idname
		 * @return integer
		 */
		public function getLastId($pdodb, $table, $idname)
		{
			$sql = "select IDENT_CURRENT('".$table."')";
			$rsId = POPDB::getConnection($pdodb)->query($sql);

			foreach ($rsId as $row)
			{
				$value = (int)$row[0];
			}

			$rsId->closeCursor();
			
			return $value;
		}

		/**
		 * Returns the required string for the createTable method
		 * @return string
		 */
		public function identityField()
		{
			return " integer identity (1, 1) not null ";
		}
		
		/**
		 * Creates the required schema to avoid errors on creating tables
		 * @param $schema
		 * @return void
		 */
		public function createSchema($pdodb, $schema)
		{
			//first, check if schema exists
			$sql    = "select * from information_schema.schemata where schema_name = '".$schema."'";
			$this->beginTransaction($pdodb);
			$rs = POPDB::getConnection($pdodb)->query($sql);
			$result = $rs->fetchAll();
			$rs->closeCursor();
			$this->commit($pdodb);
			POPDebug::log($sql);
			$count = 0;
			foreach ($result as $row)
				$count++;

			//if it doesn't, than create it
			if ($count == 0)
			{
				$sql = "create schema ".$schema;
				$this->beginTransaction($pdodb);
				POPDB::getConnection($pdodb)->exec($sql);
				$this->commit($pdodb);
				POPDebug::log($sql);
			}
			//if it doesn't, than create it
		}

		/**
		 * Returns the required SQL for field creation
		 * @param $type
		 * @param $defaultval
		 * @param $size
		 * @return void
		 */
		public function fieldCreation($type, $defaultval, $size=null)
		{
			if ($type=="date" || $type=="time")
				$sql_field =  " datetime ";
			else if ($type != "float")
				$sql_field = " ".$type." ";
			//float is created as varchar because floating
			//on SQL Server can get messy
			else if ($type == "float")
				$sql_field = " varchar(255)";

			if ($type=="varchar")
				$sql_field .= "(".$size.")";

			if (is_null($defaultval))
				$sql_field .= " null ";
			else
			{
				if ($defaultval && $type!="datetime" && $type!="date" && $type!="time" && $type!="varchar" && $type!="text")
					$sql_field .= " default ".$defaultval;
				else if ($type=="varchar")
					$sql_field .= " default '".$defaultval."'";
			}
			return $sql_field;
		}
		
		public function createPropagation()
		{
			//MSSQL can complain about cycles or multiple cascade paths
			//so we have to remove the cascade clause
			//http://support.microsoft.com/kb/321843
			$sql  = "\n\t\ton delete no action";
			$sql .= "\n\t\ton update no action";
			
			return $sql;
		}

		/**
		 * Alter table
		 * @return void
		 */
		public function alterTable()
		{
		}

		/**
		 * Drops a table
		 * @param $pdodb
		 * @param $schema
		 * @param $table
		 * @return void
		 */
		public function dropTable($pdodb, $schema, $table)
		{
			$sql  = "select * from dbo.sysobjects where id = object_id(N'".$table."') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
			$rs = POPDB::getConnection($pdodb)->query($sql);
			$result = $rs->fetchAll();
			$rs->closeCursor();
			POPDebug::log($sql);
			foreach ($result as $row)
				if($row[0])
				{
					$sql = "drop table ".$table.";\n";
					POPDB::getConnection($pdodb)->exec($sql);
					POPDebug::log($sql);
				}
		}
	}
	
	/** 
	 * Register the class for the driver
	 */
	POPDBDriverRegistry::addDriver("mssql", "PMSSQLDBDriver");
	POPDBDriverRegistry::addDriver("dblib", "PMSSQLDBDriver");

?>